*WHAT'S ACROSS THE BORDER?
*JHA, NEUMARK, AND RODRIGUEZ-LOPEZ

/*
THIS FILE CREATES THE MASTER SAMPLE AT THE CZONE-STATE-20IND LEVEL
IT CONTAINS 722 CZONES, 585 OF WHICH ARE SINGLE STATE, AND 129 OF THEM 
ARE TWO STATE, AND 8 ARE THREE-STATE
585+129*2+8*3=867 CZONE-STATES
DC WILL BE REMOVED FROM ANALYSIS (DC DATA STARTS IN 2004)
*/

include directories_build.do


* Load data at the czone-state-industry level
use "`cbpdir'`s'cbp_czone_state_wages_20ind_1990_2016.dta", clear

* Label variables
label var year "1990-2016"
label var est "Number of establishments"
label var emp "Employment"
label var qp1 "Nominal first quarter payroll"
label var ap "Nominal annual payroll"
label var sic87xx "20 industries"
label var sic87xx_desc "20 industries description"
label var czone "AADHP 722 commuting zones"
label var totpop "Total population czone-state"
label var workagepop "Total working age population czone-state"
order year czone state sic87xx emp ap qp1 est totpop workagepop 

* Merge minimum wage data
ren state statefips
merge m:1 statefips year using "`mwdir'`s'VZ_state_annual.dta"
drop if _merge==2
drop _merge
ren statefips state 

* Merge ranking data
merge m:1 sic87xx using "`cbpdir'`s'cbp_sic87xx_wagerank.dta"  
drop _merge

foreach b in "emp" "ap" "qp1" "est" {

sort czone state year 

by czone state year: egen tot`b'=total(`b')
}


* Construct nominal and real wages


	*Calculate (average) hourly wage variables
	gen wageap=ap/(2087*emp)*1000
	gen wageqp=qp1/(521.75*emp)*1000
	gen totwageap=totap/(2087*totemp)*1000
	gen totwageqp=totqp1/(521.75*totemp)*1000

	*Keep values for rest of country
	gen totwageapm=(totap-ap)/(2087*(totemp-emp))*1000
	gen totwageqpm=(totqp1-qp1)/(521.75*(totemp-emp))*1000

label var totest "Number of establishments (czone-state)"
label var totemp "Employment (czone-state)"
label var totqp1 "Nominal first quarter payroll (czone-state)"
label var totap "Nominal annual payroll (czone-state)"
label var wageap "Nominal hourly wage using AP (czone-state-ind)"
label var wageqp "Nominal hourly wage using QP (czone-state-ind)"
label var totwageap "Nominal hourly wage using AP (czone-state)"
label var totwageqp "Nominal hourly wage using QP (czone-state)"
label var totwageapm "Nominal hourly wage using AP MINUS (czone-state)"
label var totwageqpm "Nominal hourly wage using QP MINUS (czone-state)"
label var state "State FIPS"
label var state_min "State FIPS min in two-state czones"
label var state_max "State FIPS max in two-state czones"
label var multi "Multi-state czone indicator"
label var wagerank90 "Wage ranking of industry in 1990"


replace multi=0 if multi==.
gen multi_two=multi
replace multi_two=0 if three==1



compress
	
save "`datadir'`s'cbp_czone_state_20ind_sample.dta", replace

